#!/usr/bin/env python
#
# xml tree <--> sqltree converter

# $Id: main_init.py 7 2007-03-18 13:29:17Z fvamail $
"""
Init xml part of database

"""
from main_utils import *

def init ( options ) :
    connect ( options )
    conn = options.conn
    autorollback ( conn , 'create schema '+options.schema )
    autocommit ( conn , 'set search_path to '+options.schema )
    schema = options.schema
    for r in init_list :
        res = autorollback ( conn , r%vars() )
        if not options.quiet :
            print r
            print 50*'-' ,
            if res : print 'Ok'
            else : print 'Rolled back'
    disconnect ( options )
            
init_list = [ """
     drop sequence name_id_seq cascade 
	""" , """
     create sequence name_id_seq 
	""" , """
     drop table name cascade 
	""" , """
     create table name (
             name_id bigint default nextval('%(schema)s.name_id_seq')
                            primary key ,
             name text
             ) without oids 
	""" , """

     create index name_to_id_idx on name (name) 
	""" , """

     drop sequence value_id_seq cascade 
	""" , """
     create sequence value_id_seq 
	""" , """
     drop table value cascade 
	""" , """
     create table value (
             value_id bigint default nextval('%(schema)s.value_id_seq')
                             primary key ,
             value text
             ) without oids 
	""" , """

     drop sequence attr_pair_seq cascade 
	""" , """
     create sequence attr_pair_seq 
	""" , """
     drop table attr_pair cascade 
	""" , """
     create table attr_pair (
             attr_pair bigint default nextval('%(schema)s.attr_pair_seq')
                              primary key,
             name_id bigint references name on delete cascade,
             value_id bigint references value on delete cascade
             ) without oids 
	""" , """
     create index attr_pair_name_id_idx on attr_pair(name_id)
	""" , """
     create index attr_pair_value_id_idx on attr_pair(value_id)
	""" , """

     drop sequence attr_list_seq cascade 
	""" , """
     create sequence attr_list_seq 
	""" , """
     drop table attr_list cascade 
	""" , """
     create table attr_list (
             attr_list bigint default nextval('attr_list_seq') ,
             attr_pair bigint references attr_pair on delete cascade ,
             primary key ( attr_list , attr_pair )
             ) without oids 
	""" , """
     create index attr_list_attr_pair_idx on attr_list(attr_pair)
	""" , """

     create or replace function new_attr_pair (
                     bigint ,        -- attr_list
                     text ,          -- name
                     text            -- value
                     ) returns void
             language sql as '
             insert into %(schema)s.value values (DEFAULT, $3 ) ;
             insert into %(schema)s.name values (DEFAULT, $2 ) ;
             insert into %(schema)s.attr_pair values (
             DEFAULT,
             currval(\\'%(schema)s.name_id_seq\\'),
             currval(\\'%(schema)s.value_id_seq\\')) ;
             insert into %(schema)s.attr_list values
             ( $1 ,currval(\\'%(schema)s.attr_pair_seq\\'))
             ' 
	""" , """

     drop sequence fragment_seq cascade 
	""" , """
     create sequence fragment_seq   -- fragment factory
	""" , """
     drop table fragment_forest cascade 
	""" , """
     create table fragment_forest (
             fragment bigint ,
             flft bigint , frgt bigint ,
             attr_list bigint ,
             primary key ( fragment , flft )
             ) without oids 
	""" , """

     drop sequence map_seq cascade 
	""" , """
     create sequence map_seq
	""" , """
     drop table map cascade 
	""" , """
     create table map (
             map bigint ,
             lft bigint ,
             rgt bigint ,
             offs bigint ,
             primary key ( map , lft )
             ) without oids 
	""" , """

     insert into map values (0,0,9223372036854775807,0)  -- identity
	""" , """

     create or replace function map_fragment (
             bigint ,                -- map
             bigint                  -- fragment
             ) returns setof fragment_forest
             language sql immutable as '
             select  fragment ,
                     flft+lmap.offs as flft ,
                     frgt+rmap.offs as frgt,
                     attr_list
             from fragment_forest
             join map as lmap on (flft between lmap.lft and lmap.rgt)
             join map as rmap on (frgt between rmap.lft and rmap.rgt)
             where lmap.map = $1 and rmap.map = $1 and fragment = $2
             ' 
	""" , """

     drop sequence document_seq cascade 
	""" , """
     create sequence document_seq
	""" , """
     drop table document cascade 
	""" , """
     create table document (
             document bigint ,
             fragment bigint ,
             map bigint
             ) without oids 
	""" , """
     create index document_idx on document (document) 

             """ ]
#===========================================================
